REM   demo02_exception_handle.sql
REM   This file is in the D:\labs\demo folder. Execute it in a 
REM   SQL Plus session connected as HR/HR using the following syntax:
REM   @D:\labs\demo\demo02_exception_handle.sql
REM   The demo will pause numerous times.
REM   Each time the demo pauses, click ENTER to resume,
REM   until the demo script finishes at the >SQL prompt in SQL Plus.
REM
SET PAUSE ON
SET PAGESIZE 25
SET PAUSE 'HIT KEY TO CONTINUE DEMONSTRATION'
rem /*
rem ************************************************************
REM AUTHOR: DAVID JACOB-DAUB
REM PURPOSE: DEMONSTRATE HOW TO USE RAISE_APPLICATION_ERROR
REM The first trigger demonstrates that the raise_application_error
REM in and of itself does not cause an implicit rollback if an
REM EXCEPTION section is included without the RAISE_APPLICATION_ERROR
REM IN THE EXCEPTION HANDLING SECTION.
REM ************************************************************
REM */
CREATE OR REPLACE TRIGGER hire_date_trig
BEFORE UPDATE OF hire_date ON EMPLOYEES
FOR EACH ROW
BEGIN
	RAISE_APPLICATION_ERROR (-20500, 'You are not allowed to edit an employee hire_date.');
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Error ' 
		|| SQLCODE || ' ('||SQLERRM||')');
END;
/
pause
REM /*
REM ******************************************************
REM NOW WE DO AN UPDATE TO TEST;
REM ******************************************************
REM */
UPDATE EMPLOYEES SET hire_date= sysdate;
rem /*
REM ************************************************
REM Note that all the rows were updated to sysdate!
REM ************************************************
REM */
SELECT hire_date from EMPLOYEES;
pause
ROLLBACK;
pause
rem /*
REM ************************************************************
REM The second trigger demonstrates that the raise_application_error
REM in and of itself does not cause an implicit rollback but is 
REM used to give a standard error message. The statement logic
REM failure causes the implicit rollback i.e. EXCEPTION. 
REM ************************************************************
REM */
CREATE OR REPLACE TRIGGER hire_date_trig
BEFORE UPDATE OF hire_date ON EMPLOYEES
FOR EACH ROW
DECLARE
date_err EXCEPTION;
BEGIN
	IF :NEW.hire_date <>:OLD.hire_date THEN
	RAISE date_err;
	END IF;
EXCEPTION
	WHEN date_err THEN
		RAISE_APPLICATION_ERROR (-20500, 'You are not allowed to edit an employee hire_date.');
	
END;
/
pause
rem /*
REM *************************************************
REM Second update test
REM *************************************************
REM */
UPDATE EMPLOYEES SET hire_date = sysdate;
rem /*
REM *************************************************
REM NOTE THAT THE hire_date IS NOT UPDATED BUT
REM IMPLICITLY ROLLED BACK BY TRIGGER FAILURE.
REM *************************************************
REM */
SELECT hire_date from EMPLOYEES;
pause
rem /*
REM **********************************************************
REM THIS SYNTAX WILL WORK WHEN NO EXCEPTION HANDLER SECTION INCLUDED:

REM CREATE OR REPLACE TRIGGER hire_date_trig
REM   BEFORE UPDATE OF hire_date ON EMPLOYEES
REM BEGIN
REM   RAISE_APPLICATION_ERROR (
REM   -20001,
REM    'Error:  Employee Hire Date Cannot Be Modified.');
REM END;
REM *************************************************************
REM */
CREATE OR REPLACE TRIGGER hire_date_trig
  BEFORE UPDATE OF hire_date ON EMPLOYEES
BEGIN
  RAISE_APPLICATION_ERROR (
  -20001,
   'Error:  Employee Hire Date Cannot Be Modified.');
END;
/
PAUSE
rem /*
REM ************************************************************
REM UPDATED TEST
REM ************************************************************
REM */
UPDATE EMPLOYEES SET hire_date = sysdate;
PAUSE
rem /*
REM ****************************************************************
REM NOTE NO hire_dates WERE CHANGED!
REM ****************************************************************
REM */
SELECT hire_date from EMPLOYEES;
PAUSE
rem /*
REM *************************************
REM DEMO CLEANUP
REM *************************************
REM */
ROLLBACK;
DROP TRIGGER hire_date_trig
/
